package com.zucc.app.mappers;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;
import com.zucc.app.domains.Appraise;

/**
 * Created by hegy on 2018/4/13.
 */
@Mapper
@Component("appraiseMapper")
public interface AppraiseMapper {

    @Insert("INSERT INTO appraise(classId,userId,appraiseInfo,grade) VALUES"
            + "(#{classId}, #{userId},#{appraiseInfo},#{grade})")
    int insert(@Param("classId") Integer classId, @Param("userId") Integer userId, 
            @Param("appraiseInfo") String appraiseInfo,@Param("grade") Integer grade);
    
    @Select("select a.*,u.userName,u.sex from appraise a LEFT JOIN user_info u on a.userId = u.id where a.classId = #{classId}")
    List<Appraise> select(@Param("classId") Integer classId);
    
    @Select("select * from appraise where id = #{id}")
    Appraise selectInfo(@Param("id") Integer id);

    @Select("select * from appraise where classId = #{classId} and userId = #{userId}")
    Appraise checkInfo(@Param("classId") Integer classId, @Param("userId") Integer userId);

    @Select("select u.sex,count(*) count from appraise a LEFT JOIN user_info u on a.userId = u.id where a.classId = #{classId} group by sex")
    List<Appraise> countSex(@Param("classId") Integer classId);

    @Select("select case when grade < 60 then '1-59'  when (grade >=60 and grade <= 70) then '60-70'  " +
            "when (grade >70 and grade <= 80) then '70-80'  when (grade >80 and grade <= 90) then '80-90' " +
            "else '90-100' end as grade, count(*) count from appraise where classId = #{classId} group by case when grade < 60 then '1-59'  " +
            "when (grade >=60 and grade <= 70) then '60-70'  when (grade >70 and grade <= 80) then '70-80'  " +
            "when (grade >80 and grade <= 90) then '80-90' else '90-100' end  ")
    List<Map<String,Integer>> countGrade(@Param("classId") Integer classId);

    
}
